package mailsystem.persistance.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import mailsystem.Mailbox;
import mailsystem.Message;
import mailsystem.persistance.MailsystemPersistance;
import mailsystem.persistance.NewMessagePersistance;

public class NewMessageDB extends MailSystemDB implements NewMessagePersistance {
	
	public NewMessageDB(MailsystemPersistance repo) {
		super(repo);		
	}

	@Override
	public void add(Mailbox box, Message message) {
		String sql = "INSERT INTO NewMessage (message, boxId, isAudio) VALUES (?, ?, ?)";
		PreparedStatement statement = connection.prepareStatement(sql);
		
		try
		{
			statement.setString(1, message.getText());
			statement.setInt(2, box.getBoxId());
			statement.setString(3, message.isAudio() ? "A" : "N" );
			
			statement.executeUpdate();
			List<Message> messages = listAll(box);
			message.setMessageId(messages.get(messages.size() - 1).getMessageId());
		}
		catch(SQLException sqlException)
		{
			sqlException.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}
	}

	@Override
	public void delete(Message message) {
		String sql = "DELETE FROM NewMessage WHERE messageId = ?";
		PreparedStatement statement = connection.prepareStatement(sql);
		
		try
		{
			statement.setInt(1, message.getMessageId());
			
			statement.executeUpdate();
		}
		catch(SQLException sqlException)
		{
			sqlException.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}

	}

	@Override
	public List<Message> listAll(Mailbox box) {
		String sql = "SELECT * FROM NewMEssage WHERE boxId = ?";
		PreparedStatement statement = connection.prepareStatement(sql);
		ArrayList<Message> resp = new ArrayList<Message>();
		
		try
		{
			ResultSet result;
			
			statement.setInt(1, box.getBoxId());
			result = statement.executeQuery();
			
			while (result.next())
			{
				resp.add( new Message(						
						result.getString("message"),												
						result.getInt("messageId"), result.getString("isAudio").equals("A")));
			}
		}
		catch(SQLException sqlException)
		{
			sqlException.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}
		return resp;
	}
}
